SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[Print_PrescriptionDetail]
	@PrescriptionID NVARCHAR(20)
AS
With Data As (
	SELECT 
	        DrugId ,
	        DrugName ,
	        Unit ,
	        Quantity ,
	        UnitPrice ,
	        ExpDate ,
	        BatchLot FROM dbo.PrescriptionDetail WHERE PrescriptionID=@PrescriptionID
)
Select Distinct 
		data.DrugId , 
		data.DrugName ,
	    data.Unit ,
	    data.Quantity ,
	    data.UnitPrice ,
		Stuff((Select ', ' + BatchLot +' - ' +CONVERT(VARCHAR, ExpDate, 107)
			   From Data data1 
			   Where data.DrugId = data1.DrugId 
			   For XML path ('')
               ), 1, 1, '') As Remark
From Data data
Order By data.DrugId Asc
GO
